package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class VisitDTO {

	private VisitVo vo;
	private Connection conn;

	//페이지와 관련된 변수들
	int listSize= 5; //페이지당 표시되는 행수
	int blockSize = 3; //한블록당 표시되는 페이지수
	int totSize = 0; //전체 게시물의 갯수
	int totPage = 0; //전체 페이지수
	int totBlock = 0; //전체 블록수
	int nowPage = 1; //현재 페이지
	int nowBlock = 1; //현재 블럭
	int startNo = 0; //리스트 목록의 시작 위치
	int endNo = 0; //리스트 목록의 끝 위치
	int startPage = 0; //현재 블럭에서 표시되는 시작 블럭 번호
	int endPage = 0; // 현재 블럭에서 표시되는 끝 블럭 번호


	public VisitVo getVo() {
		return vo;
	}
	public void setVo(VisitVo vo) {
		this.vo = vo;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
	}
	public VisitDTO() {
		conn = new MyOracle().getConn();
	}



	public boolean visitWrite(String m){
		boolean b=false;
		try{
			String sql="insert into visit(vno, mid, content, vdate, mdate, grp, deep)"
					+"values (visit_seq.nextval, ?, ?, sysdate, sysdate, visit_seq.nextval, visit_seq.nextval)";
			PreparedStatement pst = conn.prepareStatement(sql);
		
			pst.setString(1, m);
			pst.setString(2, vo.getContent()); //content
			
			int cnt=pst.executeUpdate();

			if(cnt > 0){ b= true;}
			pst.close();
			

		}catch(Exception ex){
			ex.printStackTrace();
		}

		return b;
	}
	public boolean visitDelete(){
		boolean b=false;
		try{
			String sql = "delete from visit where vno=?";
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getVno());
			
			int cnt=pst.executeUpdate();
			
			if(cnt>0){b=true;}
			pst.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return b;
	}


	public ArrayList<VisitVo> select(){
		page();
		ArrayList<VisitVo> al = new ArrayList<>();
		String sql = "select * from (select rownum rn, c.*from(select * from visit where mid like ? or content like ? order by vdate desc)c)b where b.rn between ? and ?";
//		String sql="select * from visit order by vdate desc";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
//			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setString(2,  "%"+vo.getFind()+"%");
			pst.setInt(3, startNo);
			pst.setInt(4, endNo);

			ResultSet rs = pst.executeQuery();

			while(rs.next()){
				VisitVo v = new VisitVo();
				v.setVno(rs.getInt("vno"));
				v.setMid(rs.getString("mid"));
				v.setContent(rs.getString("content"));
				v.setVdate(rs.getString("vdate"));
				v.setMdate(rs.getString("mdate"));
				v.setGrp(rs.getInt("grp"));
				v.setDeep(rs.getString("deep"));
				
				al.add(v);
			}
			
			pst.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return al;
	}
	
	public boolean modifyVisit(){
		boolean b=false;
		try{
			String sql="update visit set content=?, mdate=sysdate where vno=?";
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getContent());
			pst.setInt(2, vo.getVno());
		
			int cnt = pst.executeUpdate();
			if(cnt>0){b=true;}
			
			pst.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return b;
	}

	public void page(){
		String sql="select count(*) cnt from visit where mid like ? or content like ? ";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+ vo.getFind()+"%");
			pst.setString(2, "%"+ vo.getFind()+"%");
			ResultSet rs = pst.executeQuery();
			if(rs.next()){ 
				nowPage = vo.getNowPage();
				
				totSize = rs.getInt("cnt");  //전체게시물의 갯수 = 10
			
				totPage = (int)Math.ceil(totSize*1.0 / listSize); // 전체페이지수=2
				
				totBlock = (int)Math.ceil(totPage*1.0/ blockSize); //전체 블록수 = 1
				
				nowBlock = (int)Math.ceil(nowPage*1.0 / blockSize); //현재 블럭 = 1
				
				//페이지의 시작과 끝 번호
				endPage = nowBlock*blockSize;  //현재 블럭에서 표시되는 끝 블럭 번호 = 3
				startPage = endPage-blockSize+1; //현재 블럭에서 표시되는 시작 블럭 번호 = 1
				//if(startPage<=0){ startPage=1;}
				
				//게시물의 시작 번호와 끝 번호
				endNo = nowPage * listSize;      //리스트 목록의 끝 위치 = 5
				startNo = endNo - listSize +1; //리스트 목록의 시작 위치 = 1
				//endPage, endNo 보정값
				if( endPage > totPage ) endPage = totPage; 
				if( endNo > totSize ) endNo = totSize;
			}
	
			System.out.println( " <li>totSize = " + totSize );
			System.out.println( " <li>totPage = " + totPage );
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
	}
	
	public boolean repleInput(String m){
		
		boolean b=false;
		try{
			String sql = "insert into visit(vno, mid, content, vdate, mdate, grp, deep)" 
						+" values(visit_seq.nextval, ?, ?, sysdate, sysdate, ?, ?||'-'||visit_seq.nextval)";
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, m);
			pst.setString(2, vo.getRcontent()); //content
			pst.setInt(3, vo.getVno()); //grp
			pst.setString(4, String.valueOf(vo.getVno())); //deep
			
			int cnt = pst.executeUpdate();
			if(cnt>0){
				b=true;
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return b;
	} 
	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

}
